let db = require('../db.js');

//员工信息拉取

exports.employeesList = async function (start,length,id) {
   
    start = parseInt(start);
    length = parseInt(length);
     let sql=`
     SELECT SQL_CALC_FOUND_ROWS *,
     DATE_FORMAT(createdAt,'%Y-%m-%d %H:%i') AS createdAt
     FROM   employees 
     `
     sql+= `where isdelete='n'`
     if(id){
         sql+=` and departmentId =:id `
     }
    
     sql+=`  limit :start,:length ;`
     sql += `   SELECT FOUND_ROWS() sum ;`
     sql = sql.replace('AND', 'WHERE');
 
     let data = await db.query(sql,{start,length,id});
     return data[0]; 
}


//获取所有部门信息

exports.departmentList = async function(req,res){
    let sql=`
    select id,name  from department 
    `
    let data  =  await db.query(sql);
    return data[0];
}


//判断员工工号是否重复

exports.judgeNum = async function (num){
    let sql=`
    select  * from  employees  where num =:num  AND isdelete='n'
    `
    let data =  await db.query(sql,{num});
    return data[0];

}

//添加员工信息
exports.addEmployees = async function(num,name,departmentId,departmentName,phone,imgUrl){
  let sql=`
    INSERT  INTO  employees(num,name,departmentId,departmentName,phone,url)
    VALUES(:num,:name,:departmentId,:departmentName,:phone,:imgUrl)
  `
  let data = await db.query(sql,{num,name,departmentId,departmentName,phone,imgUrl});
  return data[0];


}

//获取员工信息详情
exports.employeesDetail  = async function(id){
    let sql= `
    SELECT *,
    DATE_FORMAT(createdAt,'%Y-%m-%d %H:%i') AS createdAt
    FROM   employees  where id =:id
    `
    let  data = await db.query(sql,{id});
    return data[0][0];
}

//判断修改时员工的工号是否重复

exports.judgeEditNum  = async  function(num,id){
    let sql=`
    select  * from  employees  where num =:num AND id!=:id AND isdelete='n'
    `
   let data  =await db.query(sql,{num,id});
   return data[0];
}

//更新员工信息
exports.updateEmployees  = async function(id,num,name,departmentId,departmentName,imgUrl){
let sql=`
  UPDATE employees  SET 
  num=:num,
  name=:name,
  departmentId=:departmentId,
  departmentName=:departmentName,
  url=:imgUrl
  where id=:id

`
let data=  await db.query(sql,{id,num,name,departmentId,departmentName,imgUrl});
return data[0];
}

//删除员工信息
exports.removeEmployees  = async function (id){
    let sql=`
    UPDATE  employees SET  
    isdelete ='y'
    where id =:id
    `
    let data  = await db.query(sql,{id});
    return data[0];
}